package com.pucong.gitee.database.backup.utils;

import com.google.common.base.Joiner;
import com.pucong.gitee.database.backup.common.entity.database.dto.DatabaseSqlExecVo;
import com.pucong.gitee.database.backup.configuration.exception.BusinessException;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 校验sql
 *
 * @author pucong
 */
public class SqlUtils {

    /**
     * 判断是否是查询语句
     *
     * @return
     */
    public static Boolean isSelectSql(String sqlOld) {
        String sql = " " + sqlOld.toLowerCase();
        sql = sql.replaceAll("\r", "\r ");
        sql = sql.replaceAll("\n", "\n ");
        if (sql.toLowerCase().contains(" alert ") || sql.toLowerCase().contains(" drop ") ||
                sql.toLowerCase().contains(" update ") ||
                sql.toLowerCase().contains(" insert ") || sql.toLowerCase().contains(" delete ")) {
            return false;
        }
        return true;
    }

    public static List<String> getInsertSqlListByList(List<Map<String, Object>> dataPage, String tableName) {
        List<String> sqlList = new ArrayList<>();
        if (dataPage.size() > 0) {
            for (Map<String, Object> stringObjectHashMap : dataPage) {
                //获取字段信息
                Set<String> filedList = stringObjectHashMap.keySet();
                String fileStr = Joiner.on(",").join(filedList);

                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.append("insert into ");
                stringBuilder.append(tableName);
                stringBuilder.append(" (");
                stringBuilder.append(fileStr);
                stringBuilder.append(") values (");
                List valList = new ArrayList(filedList.size());
                for (String filed : filedList) {
                    StringBuilder valStb = new StringBuilder();
                    final Object val = stringObjectHashMap.get(filed);
                    if (val instanceof String) {
                        valStb.append("'");
                        valStb.append(val);
                        valStb.append("'");
                    } else if (val instanceof Integer) {
                        valStb.append(val);
                    } else if (val instanceof Date) {
                        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        valStb.append("'");
                        valStb.append(format.format(val));
                        valStb.append("'");
                    }
                    valList.add(valStb.toString());
                }
                stringBuilder.append(Joiner.on(",").join(valList));
                stringBuilder.append(");");
                sqlList.add(stringBuilder.toString());
            }
        }
        return sqlList;
    }

    public static List<DatabaseSqlExecVo> getDeleteSqlListByList(List<Map<String, Object>> dataList, String tableName) {
        List<DatabaseSqlExecVo> sqlList = new ArrayList<>();
        if (dataList.size() > 0) {
            for (Map<String, Object> stringObjectHashMap : dataList) {
                Set<String> filedList = stringObjectHashMap.keySet();
                DatabaseSqlExecVo vo = new DatabaseSqlExecVo();
                Object[] params = new Object[filedList.size()];
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.append("delete from ");
                stringBuilder.append(tableName);
                stringBuilder.append(" where ");
                stringBuilder.append(" 1=1 ");
                List valList = new ArrayList(filedList.size());
                int index = 0;
                for (String filed : filedList) {
                    StringBuilder valStb = new StringBuilder();
                    valStb.append(" and ");
                    valStb.append(filed);
                    valStb.append("=?");
                    valList.add(valStb.toString());
                    Object val = stringObjectHashMap.get(filed);
                    params[index] = val;
                    index++;
                }
                stringBuilder.append(Joiner.on(" ").join(valList));
                stringBuilder.append(";");

                vo.setSql(stringBuilder.toString());
                vo.setParams(params);
                sqlList.add(vo);
            }
        }
        return sqlList;
    }
}
